
DROP TABLE CommodityDetails IF EXISTS
go


DROP TABLE TradeHistory IF EXISTS
go


DROP TABLE CredentialsHistory IF EXISTS
go


DROP TABLE TradeFeedback IF EXISTS
go


DROP TABLE VendorSupply IF EXISTS
go


DROP TABLE VendorDelivery IF EXISTS
go


DROP TABLE Trade IF EXISTS
go


DROP TABLE Buyer IF EXISTS
go


DROP TABLE EnvironmentCommodityExpiry IF EXISTS
go


DROP TABLE CommodityEnvironment IF EXISTS
go


DROP TABLE Environment IF EXISTS
go


DROP TABLE Vendor IF EXISTS
go


DROP TABLE Game IF EXISTS
go


DROP TABLE Delivery IF EXISTS
go


DROP TABLE Credentials IF EXISTS
go


DROP TABLE Commodity IF EXISTS
go



CREATE TABLE Commodity (
       C_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , C_NAME CHAR(30)
)
go



CREATE TABLE Credentials (
       CR_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , CR_EMAIL_ADDRESS CHAR(30)
     , CR_PHONE CHAR(20)
     , CR_STATUS CHAR(10)
)
go



CREATE TABLE Delivery (
       D_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , D_DESCRIPTION CHAR(60)
)
go



CREATE TABLE Game (
       G_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , G_NAME CHAR(20)
)
go



CREATE TABLE Vendor (
       V_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , V_CR_ID INTEGER NOT NULL
     , CONSTRAINT FK_Vendor_1 FOREIGN KEY (V_CR_ID)
                  REFERENCES Credentials (CR_ID)
)
go



CREATE TABLE Environment (
       E_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , E_G_ID INTEGER NOT NULL
     , E_NAME CHAR(30)
     , CONSTRAINT FK_Environment_1 FOREIGN KEY (E_G_ID)
                  REFERENCES Game (G_ID)
)
go



CREATE TABLE CommodityEnvironment (
       CE_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , CE_E_ID INTEGER NOT NULL
     , CE_C_ID INTEGER NOT NULL
     , CONSTRAINT FK_CommodityEnvironment_1 FOREIGN KEY (CE_E_ID)
                  REFERENCES Environment (E_ID)
     , CONSTRAINT FK_CommodityEnvironment_2 FOREIGN KEY (CE_C_ID)
                  REFERENCES Commodity (C_ID)
)
go



CREATE TABLE EnvironmentCommodityExpiry (
       ECE_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , ECE_CE_ID INTEGER NOT NULL
     , ECE_DATE DATE
     , ECE_AVAILABLE_NOW CHAR(3)
     , CONSTRAINT FK_EnvironmentCommodityExpiry_1 FOREIGN KEY (ECE_CE_ID)
                  REFERENCES CommodityEnvironment (CE_ID)
)
go



CREATE TABLE Buyer (
       B_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , B_CR_ID INTEGER NOT NULL
     , CONSTRAINT FK_Buyer_1 FOREIGN KEY (B_CR_ID)
                  REFERENCES Credentials (CR_ID)
)
go



CREATE TABLE Trade (
       T_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , T_V_ID INTEGER NOT NULL
     , T_B_ID INTEGER NOT NULL
     , T_ECE_ID INTEGER NOT NULL
     , T_QUANTITY INTEGER
     , T_PRICE FLOAT
     , T_STATUS CHAR(10)
     , CONSTRAINT FK_Trade_1 FOREIGN KEY (T_V_ID)
                  REFERENCES Vendor (V_ID)
     , CONSTRAINT FK_Trade_2 FOREIGN KEY (T_B_ID)
                  REFERENCES Buyer (B_ID)
     , CONSTRAINT FK_Trade_3 FOREIGN KEY (T_ECE_ID)
                  REFERENCES EnvironmentCommodityExpiry (ECE_ID)
)
go



CREATE TABLE VendorDelivery (
       VD_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , VD_V_ID INTEGER NOT NULL
     , VD_D_ID INTEGER NOT NULL
     , CONSTRAINT FK_VendorDelivery_1 FOREIGN KEY (VD_V_ID)
                  REFERENCES Vendor (V_ID)
     , CONSTRAINT FK_VendorDelivery_2 FOREIGN KEY (VD_D_ID)
                  REFERENCES Delivery (D_ID)
)
go



CREATE TABLE VendorSupply (
       VS_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , VS_V_ID INTEGER NOT NULL
     , VS_VD_ID INTEGER NOT NULL
     , VS_ECE_ID INTEGER NOT NULL
     , VS_PRICE FLOAT
     , VS_QUANTITY INTEGER
     , VS_REFERENCE CHAR(30)
     , CONSTRAINT FK_VendorSupply_1 FOREIGN KEY (VS_V_ID)
                  REFERENCES Vendor (V_ID)
     , CONSTRAINT FK_VendorSupply_2 FOREIGN KEY (VS_VD_ID)
                  REFERENCES VendorDelivery (VD_ID)
     , CONSTRAINT FK_VendorSupply_3 FOREIGN KEY (VS_ECE_ID)
                  REFERENCES EnvironmentCommodityExpiry (ECE_ID)
)
go



CREATE TABLE TradeFeedback (
       TF_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , TF_T_ID INTEGER NOT NULL
     , TF_V_ID INTEGER NOT NULL
     , TF_B_ID INTEGER NOT NULL
     , T_VENDOR_COMMENTS CHAR(60)
     , T_BUYER_COMMENTS CHAR(60)
     , T_BTOV_RANK_1 INTEGER
     , T_VTOB_RANK_1 INTEGER
     , CONSTRAINT FK_TradeFeedback_1 FOREIGN KEY (TF_T_ID)
                  REFERENCES Trade (T_ID)
     , CONSTRAINT FK_TradeFeedback_2 FOREIGN KEY (TF_V_ID)
                  REFERENCES Vendor (V_ID)
     , CONSTRAINT FK_TradeFeedback_3 FOREIGN KEY (TF_B_ID)
                  REFERENCES Buyer (B_ID)
)
go



CREATE TABLE CredentialsHistory (
       CH_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , CH_STATUS CHARACTER(30)
     , CH_CR_ID INTEGER NOT NULL
     , CONSTRAINT FK_CredentialsHistory_1 FOREIGN KEY (CH_CR_ID)
                  REFERENCES Credentials (CR_ID)
)
go



CREATE TABLE TradeHistory (
       TH_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , TH_T_ID INTEGER NOT NULL
     , TH_TIMESTAMP DATETIME
     , TH_COMMENTS CHAR(30)
     , CONSTRAINT FK_TradeHistory_1 FOREIGN KEY (TH_T_ID)
                  REFERENCES Trade (T_ID)
)
go



CREATE TABLE CommodityDetails (
       CD_ID INTEGER NOT NULL IDENTITY PRIMARY KEY
     , CD_C_ID INTEGER NOT NULL
     , CONSTRAINT FK_CommodityDetails_1 FOREIGN KEY (CD_C_ID)
                  REFERENCES Commodity (C_ID)
)
go



